package persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import dominio.Cliente;
import dominio.Pedido;
import dominio.PersistentObject;

public class ClienteDAO  extends DataAccessObject {
	
	public static Cliente leerDesdeResultSet(ResultSet rs) throws PersistentException {
		 try {
			 	List<Pedido> pedidos=new ArrayList<Pedido>();
	            Cliente c = new Cliente();
	            c.setId(rs.getInt("Id"));
	            c.setNombre(rs.getString("nombre"));
	            pedidos=PedidoDAO.buscarPorIdCliente(c);
	            c.setPedidos(pedidos);
	            c.setPrioridad(rs.getInt("Prioridad"));
	         
	            return c;
	        } catch (SQLException e) {
	        }
	        return null;
	    }

	@Override
	public void delete(PersistentObject obj) throws PersistentException {
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			Cliente cliente = (Cliente) obj;
			conn = getConnection();
			String sql = "DELETE FROM Cliente WHERE id = ?";
			pst = conn.prepareStatement(sql);
			pst.setInt(1, cliente.getId());
			int registro = pst.executeUpdate();
			if(registro == 0)
				throw new ObjectNotFoundException("No se encontro el cliente a eliminar");
			commitTransaction();			
		}
		catch(SQLException sqex) {
			rollBackTransaction();
			throw new PersistentException(sqex.getMessage());
		}
		finally {
			try {
				conn.close();
				if(pst != null) pst.close();
			}
			catch(SQLException sqex) {}
		}
	}

	@Override
	public void insert(PersistentObject obj) throws PersistentException {
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			Cliente cliente = (Cliente) obj;
			int id=DataAccessObject.proximoOid();
			conn = getConnection();
			String sql = "INSERT INTO Cliente(Id,Nombre,Prioridad) VALUES(?,?,?)";
			pst = conn.prepareStatement(sql);
			pst.setInt(1, id);
			pst.setString(2, cliente.getNombre());
			pst.setInt(3, cliente.getPrioridad());
			int registro = pst.executeUpdate();
			if(registro == 0)
				throw new PersistentException("No se pudo insertar el Cliente");
			commitTransaction();			
		}
		catch(SQLException sqex) {
			rollBackTransaction();
			throw new PersistentException(sqex.getMessage());
		}
		finally {
			try {
				conn.close();
				if(pst != null) pst.close();
			}
			catch(SQLException sqex) {}
		}
	}

	@Override
	public List<Object> selectAll() throws PersistentException {
		Connection conn = null;
		Statement pst = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(true);
			String sql = "SELECT * FROM Cliente";
			pst = conn.createStatement();
			
			rs = pst.executeQuery(sql);
			
			
			List<Object> clientes = new ArrayList<Object>();
			Cliente cliente = null;
			List<Pedido> pedidos=new ArrayList<Pedido>();
			while(rs.next()) {
				cliente= new Cliente();
				pedidos=null;
				cliente.setId(rs.getInt("Id"));
				cliente.setNombre(rs.getString("Nombre"));
				cliente.setPrioridad(rs.getInt("Prioridad"));
				pedidos=PedidoDAO.buscarPorIdCliente(cliente);
				cliente.setPedidos(pedidos);
				clientes.add(cliente);
			}
			return clientes;
		}
		catch(SQLException sqex) {
			throw new PersistentException(sqex.getMessage());
		}
		finally {
			try {
				conn.close();
				if(pst != null) pst.close();
				if(rs != null) rs.close();
			}
			catch(SQLException sqex) {}
		}
	}

	@Override
	public void update(PersistentObject obj) throws PersistentException {
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			Cliente cliente = (Cliente) obj;
			conn = getConnection();
			String sql = "UPDATE Cliente SET Nombre=?,Prioridad=? WHERE Id=?";
			pst = conn.prepareStatement(sql);
			pst.setInt(3, cliente.getId());
			pst.setString(1, cliente.getNombre());
			pst.setInt(2, cliente.getPrioridad());
			int registro = pst.executeUpdate();
			if(registro == 0)
				throw new PersistentException("No se pudo actualizar el Cliente");
			commitTransaction();			
			if(cliente.getPedidos()!=null){
			for(int i=0;i<cliente.getPedidos().size();i++){
				sql = "UPDATE Pedido SET IdCliente=? WHERE Id=?";
				pst = conn.prepareStatement(sql);
				pst.setInt(1, cliente.getId());
				pst.setInt(2, cliente.getPedidos().get(i).getId());
				registro = pst.executeUpdate();
				if(registro == 0)
					throw new PersistentException("No se pudo actualizar el Cliente");
				commitTransaction();	
			}
			}
		}
		catch(SQLException sqex) {
			rollBackTransaction();
			throw new PersistentException(sqex.getMessage());
		}
		finally {
			try {
				conn.close();
				if(pst != null) pst.close();
			}
			catch(SQLException sqex) {}
		}
	}

	public static Cliente buscarPorId(int id) throws PersistentException {
		Connection conn = null;
		Statement pst = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(true);
			String sql = "SELECT * FROM Cliente WHERE Id="+id;
			pst = conn.createStatement();
			rs = pst.executeQuery(sql);
			Cliente cliente = null;
			List<Pedido> pedidos=new ArrayList<Pedido>();
			while(rs.next()) {
				cliente= new Cliente();
				pedidos=null;
				cliente.setId(rs.getInt("Id"));
				cliente.setNombre(rs.getString("Nombre"));
				cliente.setPrioridad(rs.getInt("Prioridad"));
				pedidos=PedidoDAO.buscarPorIdCliente(cliente);
				cliente.setPedidos(pedidos);
			}
			return cliente;
		}
		catch(SQLException sqex) {
			throw new PersistentException(sqex.getMessage());
		}
		finally {
			try {
				conn.close();
				if(pst != null) pst.close();
				if(rs != null) rs.close();
			}
			catch(SQLException sqex) {}
		}
	}

	public static List<Cliente> buscarPorNombre(String nombre) throws PersistentException {
		Connection conn = null;
		Statement pst = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(true);
			String sql = "SELECT * FROM Cliente WHERE Nombre LIKE '%"+ nombre +"%'";
			pst = conn.createStatement();
			rs = pst.executeQuery(sql);
			List<Cliente> listaClientes = new ArrayList<Cliente>();
			while(rs.next()) {
				Cliente cliente= leerDesdeResultSet(rs);
				listaClientes.add(cliente);
			}
			return listaClientes;
		}
		catch(SQLException sqex) {
			throw new PersistentException(sqex.getMessage());
		}
		finally {
			try {
				conn.close();
				if(pst != null) pst.close();
				if(rs != null) rs.close();
			}
			catch(SQLException sqex) {}
		}
	}

	public static void buscarPorNombreExiste(String nombre) throws PersistentException,ObjectNotFoundException,ObjetoRepetidoException {
		Connection conn = null;
		Statement pst = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(true);
			String sql = "SELECT * FROM Cliente WHERE Nombre ='" + nombre + "'";
			
			pst = conn.createStatement();
			rs = pst.executeQuery(sql);
			if(rs.next()==false){
				throw new ObjectNotFoundException();
			}else{
				throw new ObjetoRepetidoException();
			}

		}
		catch(SQLException sqex) {
			throw new PersistentException(sqex.getMessage());
		}
		finally {
			try {
				conn.close();
				if(pst != null) pst.close();
				if(rs != null) rs.close();
			}
			catch(SQLException sqex) {}
		}
	}
	
	public static Cliente buscarPorIdPedido(int id) throws PersistentException {
		Connection conn = null;
		Statement pst = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(true);
			String sql = "SELECT IdCliente FROM Pedido WHERE Id="+id;
			pst = conn.createStatement();
			rs = pst.executeQuery(sql);
			int idCliente = 0;
			while(rs.next()) {
				idCliente=rs.getInt("IdCliente");
			}
			return buscarPorId(idCliente);
		}
		catch(SQLException sqex) {
			throw new PersistentException(sqex.getMessage());
		}
		finally {
			try {
				conn.close();
				if(pst != null) pst.close();
				if(rs != null) rs.close();
			}
			catch(SQLException sqex) {}
		}
	}
}
